﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
using INTEX.DataBase;
namespace WPFExcelReport
{

    public class JLTEKGBSUPPData : BaseFunction
    {

        public string flag = "";
        public ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        public INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        public INTEXBL INTEXBL
        {
            get
            {
                return app.INTEXBL;
            }
        }
        SETINTEXDB INTEXDB;
        public JLTEKGBSUPPData()
            : base()
        {
            INTEXDB = new SETINTEXDB();
        }
        public JLTEKGBSUPPData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
            INTEXDB = new SETINTEXDB();
        }

        public DataSet GetEKGBSUPP(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "EKGBSUPP";
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT SUPP AS CODE,SUPPNAME AS DES,sum(QTY) as QTY,sum(INQTY) AS INQTY "
                    + " FROM (SELECT DISTINCT A.EKKAUFKRS||'-'||TO_NUMBER(A.EKKAUFLFD) AS ORDERNO,   "
                    + " TO_NUMBER(A.EKKLIEFNR) AS SUPP,A.EKKBDAT,TRIM(C.LSNAME2) AS SUPPNAME,   "
                    + " TRIM(utl_raw.cast_to_varchar2(B.EKPART)) AS ARTICLE, "
                    + " TRIM(E.GSHANDLNR) AS ARTDES,B.EKPME1*0.01 AS QTY,   "
                    + " (SELECT SUM(D.GPEINBRUT*0.01) FROM GPDAT D WHERE B.EKPMANDANT = D.GPMANDANT  "
                    + " AND B.EKPAUFKRS = D.GPBESTKRS AND B.EKPAUFLFD = D.GPBESTLFD    "
                    + " AND B.EKPAUFPOS = D.GPBESTPOS and D.GPEINDAT >=" + FROMDNDATE + "  "
                    + " AND D.GPEINDAT <=" + ENDDNDATE + " ) as INQTY,B.EKPPRME*0.001 AS PRICE, "
                    + " TRIM(A.EKKKNTRNRL) AS SCHEDNO  FROM EKKOPF A,EKPOS B,LIEFDAT C,GSDAT E   "
                    + " WHERE A.EKKMANDANT = B.EKPMANDANT AND A.EKKAUFKRS = B.EKPAUFKRS   "
                    + " AND A.EKKAUFLFD = B.EKPAUFLFD AND A.EKKMANDANT = C.LSMANDANT   "
                    + " AND A.EKKLIEFNR = C.LSLIEFNR  AND B.EKPSA = 'PO'  AND B.EKPMANDANT = E.GSMANDANT   "
                    + " AND utl_raw.cast_to_varchar2(B.EKPART) = E.GSART   "
                    + " AND utl_raw.cast_to_varchar2(B.EKPVAR) = E.GSVAR   "
                    + " AND A.EKKBDAT >=" + FROMDATE + " AND A.EKKBDAT <=" + ENDDATE + " )  "
                    + " GROUP BY SUPP,SUPPNAME order by QTY DESC,INQTY DESC "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DES"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DES"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetEKGBYARN(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "EKGBYARN";
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT ARTICLE AS CODE,ARTDES AS DES,sum(QTY) as QTY,sum(INQTY) AS INQTY "
                     + " FROM (SELECT DISTINCT A.EKKAUFKRS||'-'||TO_NUMBER(A.EKKAUFLFD) AS ORDERNO,   "
                    + " TO_NUMBER(A.EKKLIEFNR) AS SUPP,A.EKKBDAT,TRIM(C.LSNAME2) AS SUPPNAME,   "
                    + " TRIM(utl_raw.cast_to_varchar2(B.EKPART)) AS ARTICLE, "
                    + " TRIM(E.GSHANDLNR) AS ARTDES,B.EKPME1*0.01 AS QTY,   "
                    + " (SELECT SUM(D.GPEINBRUT*0.01) FROM GPDAT D WHERE B.EKPMANDANT = D.GPMANDANT  "
                    + " AND B.EKPAUFKRS = D.GPBESTKRS AND B.EKPAUFLFD = D.GPBESTLFD    "
                    + " AND B.EKPAUFPOS = D.GPBESTPOS and D.GPEINDAT >=" + FROMDNDATE + "  "
                    + " AND D.GPEINDAT <=" + ENDDNDATE + " ) as INQTY,B.EKPPRME*0.001 AS PRICE, "
                    + " TRIM(A.EKKKNTRNRL) AS SCHEDNO  FROM EKKOPF A,EKPOS B,LIEFDAT C,GSDAT E   "
                    + " WHERE A.EKKMANDANT = B.EKPMANDANT AND A.EKKAUFKRS = B.EKPAUFKRS   "
                    + " AND A.EKKAUFLFD = B.EKPAUFLFD AND A.EKKMANDANT = C.LSMANDANT   "
                    + " AND A.EKKLIEFNR = C.LSLIEFNR  AND B.EKPSA = 'PO'  AND B.EKPMANDANT = E.GSMANDANT   "
                    + " AND utl_raw.cast_to_varchar2(B.EKPART) = E.GSART   "
                    + " AND utl_raw.cast_to_varchar2(B.EKPVAR) = E.GSVAR   "
                    + " AND A.EKKBDAT >=" + FROMDATE + " AND A.EKKBDAT <=" + ENDDATE + " )  "
                    + " GROUP BY ARTICLE,ARTDES order by QTY DESC,INQTY DESC "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DES"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DES"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet BuildJLTEKGBSUPPData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                if (flag == "SUPP")
                {
                    setResult = GetEKGBSUPP(dwUI);
                }
                if (flag == "YARN")
                {
                    setResult = GetEKGBYARN(dwUI);
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet QueryEKPOS(DataRow dwInput, string TYPE, string FROMDATE, string ENDDATE)
        {
            DataSet SReturn = new DataSet();
            DataSet SResult = new DataSet();
            try
            {
                DataRow dwData = INTEXDB.ICC_EKPOS.NewRow();
                switch (TYPE)
                {
                    case "SUPP":
                        dwData["EKPMANDANT"] = "00";
                        dwData["EKPLIEFNR"] =Right("00000000"+ dwInput["CODE"].ToString(),8);
                        dwData["EKPLDAT"] = FROMDATE;
                        dwData["EKPDRUDAT"] = ENDDATE;
                        SResult = INTEXDBC.QueryICC_EKPOS(dwData, 7);
                        break;
                    case "YARN":
                         dwData["EKPMANDANT"] = "00";
                         dwData["EKPART"] = Left(dwInput["CODE"].ToString().PadRight(20), 20);
                        dwData["EKPLDAT"] = FROMDATE;
                        dwData["EKPDRUDAT"] = ENDDATE;
                        SResult = INTEXDBC.QueryICC_EKPOS(dwData, 8);
                        break;
                }
                SReturn.Merge(SResult); 
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return SReturn;
        }
    }
}